﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using DA;
using System.Data;
namespace BLL
{
   
    public class ThongKeBLL
    {
        private MyQueryDA myqueryDA;
        public ThongKeBLL()
        {
            myqueryDA = new MyQueryDA();
        }

        #region ToBuom
        public DataSet ToBuom_GetDSKhachHang(int _thangbaocao, int _nambaocao)
        {
            string query = String.Format("select DISTINCT KHACHHANG.MaKH,HoTen from KHACHHANG,DIAOC,PHIEUDANGKY where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and MaToBuom >'1' and MONTH(TuNgay)='{0}' and YEAR(TuNgay)='{1}'", _thangbaocao, _nambaocao);
            return myqueryDA.doQuery(query);
        }
        public DataSet ToBuom_GetDSKhachHangDaGiaHan(int _thangbaocao, int _nambaocao)
        {
            string query = String.Format("select DISTINCT KHACHHANG.MaKH,HoTen from KHACHHANG,DIAOC,PHIEUDANGKY,PHIEUGIAHAN where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and PHIEUDANGKY.MaPhieuDangKy=PHIEUGIAHAN.MaPhieuDangKy and MaToBuom >'1' and MONTH(NgayGiaHan)='{0}' and YEAR(NgayGiaHan)='{1}' and KHACHHANG.MaKH not in (select DISTINCT KHACHHANG.MaKH from KHACHHANG,DIAOC,PHIEUDANGKY where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and MaToBuom >'1' and MONTH(TuNgay)=  ''{0}' and YEAR(TuNgay)='{1}')", _thangbaocao, _nambaocao);
            return myqueryDA.doQuery(query);
        }
        #endregion

        #region Bao
        public DataSet Bao_GetDSKhachHang(int _thangbaocao, int _nambaocao)
        {
            string query = String.Format("select DISTINCT KHACHHANG.MaKH,HoTen from KHACHHANG,DIAOC,PHIEUDANGKY where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and MaQCBao >'1' and MONTH(TuNgay)='{0}' and YEAR(TuNgay)='{1}'", _thangbaocao, _nambaocao);
            return myqueryDA.doQuery(query);
        }
        public DataSet Bao_GetDSKhachHangDaGiaHan(int _thangbaocao, int _nambaocao)
        {
            string query = String.Format("select DISTINCT KHACHHANG.MaKH,HoTen from KHACHHANG,DIAOC,PHIEUDANGKY,PHIEUGIAHAN where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and PHIEUDANGKY.MaPhieuDangKy=PHIEUGIAHAN.MaPhieuDangKy and MaQCBao >'1' and MONTH(NgayGiaHan)='{0}' and YEAR(NgayGiaHan)='{1}' and KHACHHANG.MaKH not in (select DISTINCT KHACHHANG.MaKH from KHACHHANG,DIAOC,PHIEUDANGKY where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and MaQCBao >'1' and MONTH(TuNgay)=  ''{0}' and YEAR(TuNgay)='{1}')", _thangbaocao, _nambaocao);
            return myqueryDA.doQuery(query);
        }
        #endregion

        #region Bang
        public DataSet Bang_GetDSKhachHang(int _thangbaocao, int _nambaocao)
        {
            string query = String.Format("select DISTINCT KHACHHANG.MaKH,HoTen from KHACHHANG,DIAOC,PHIEUDANGKY where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and MaQCBang >'1' and MONTH(TuNgay)='{0}' and YEAR(TuNgay)='{1}'", _thangbaocao, _nambaocao);
            return myqueryDA.doQuery(query);
        }
        public DataSet Bang_GetDSKhachHangDaGiaHan(int _thangbaocao, int _nambaocao)
        {
            string query = String.Format("select DISTINCT KHACHHANG.MaKH,HoTen from KHACHHANG,DIAOC,PHIEUDANGKY,PHIEUGIAHAN where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and PHIEUDANGKY.MaPhieuDangKy=PHIEUGIAHAN.MaPhieuDangKy and MaQCBang >'1' and MONTH(NgayGiaHan)='{0}' and YEAR(NgayGiaHan)='{1}' and KHACHHANG.MaKH not in (select DISTINCT KHACHHANG.MaKH from KHACHHANG,DIAOC,PHIEUDANGKY where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and MaQCBang >'1' and MONTH(TuNgay)=  ''{0}' and YEAR(TuNgay)='{1}')", _thangbaocao, _nambaocao);
            return myqueryDA.doQuery(query);
        }
        #endregion

        #region DoanhThu
        public DataSet DoanhThu_GetDSKhachHang(int _thangbaocao, int _nambaocao)
        {
            string query = String.Format("select HoTen,SoTien from KHACHHANG,DIAOC,PHIEUDANGKY where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and MONTH(TuNgay)='{0}' and YEAR(TuNgay)='{1}'", _thangbaocao, _nambaocao);
            return myqueryDA.doQuery(query);
        }
        public DataSet DoanhThu_GetDSKhachHangDaGiaHan(int _thangbaocao, int _nambaocao)
        {
            string query = String.Format("select HoTen,TongTienGiaHan as SoTien from KHACHHANG,DIAOC,PHIEUDANGKY,PHIEUGIAHAN where KHACHHANG.MaKH=DIAOC.MAKH and DIAOC.MaDiaOc=PHIEUDANGKY.MaDiaOc and PHIEUDANGKY.MaPhieuDangKy=PHIEUGIAHAN.MaPhieuDangKy and MONTH(TuNgay)='{0}' and YEAR(TuNgay)='{1}'", _thangbaocao, _nambaocao);
            return myqueryDA.doQuery(query);
        }
        #endregion
    }
}
